<?php
namespace app\common\service;

use app\common\helper\WeixinHelper;
use think\Model;

class DataStatisticsService extends BaseService
{

    /**
     * 获取粉丝变化量
     * @param $sid
     * @param $date
     * @return array
     */
    public function getFansChangeCount($sid, $date){
        //获取粉丝增量
        $subscribe = $this->getFansSubscribeCount($sid, $date);

        //获取粉丝取消关注数据
        $unsubscribe = $this->getFansUnsubscribeCount($sid, $date);

        $data = [
            'subscribe' => $subscribe,
            'unsubscribe' => $unsubscribe
        ];
        return $data;
    }

    /**
     * 获取某段时间内粉丝增量
     * @param $sid
     * @param $date
     * @return array
     */
    public function getFansSubscribeCount($sid, $date){
        $result = [];//最终返回数据

        $where = [
            'sid' => $sid,
            'is_unsubscribe' => 0
        ];
        //查询今天 date=today、近7天 date=week、近30天 date=month
        $where_time = $date == 'today' ? 'today' : ($date == 'week' ? '-7 days' : '-30 days');

        //查询时间段内的新增数量
        $count = db('user_integral')
                    ->where($where)
                    ->whereTime('subscribe_time', '>', $where_time)
                    ->count();


        if($date == 'today'){
            //初始化数据
            $init_month = $this->initDayTime();
            //以不同的节点分组
            //存在8h误差
            $field = 'FROM_UNIXTIME(subscribe_time+8*60*60, \'%k\') as time,count(*) as count';
        } elseif($date == 'week'){
            //初始化数据
            $init_month = $this->initWeekTime();
            //以不同的节点分组
            $field = 'FROM_UNIXTIME(subscribe_time, \'%m-%d\') as time,count(*) as count';
        } elseif($date == 'month'){
            //初始化数据
            $init_month = $this->initMonthTime();
            //以不同的节点分组
            $field = 'FROM_UNIXTIME(subscribe_time, \'%m-%d\') as time,count(*) as count';
        }

        //如果增量为0，则直接返回初始化数据
        if($count == 0){
            $result = [
                'count' => 0,
                'count_node' => $init_month
            ];
            return $result;
        }

        //查询时间段内每个时间节点的增量
        $count_node = db('user_integral')
            ->field($field)
            ->where($where)
            ->whereTime('subscribe_time', '>', $where_time)
            ->group('time')
            ->select();


        //保存以日期为key的数据
        $count_data = [];
        if($count_node){
            foreach($count_node as $k => $v){
                $count_data[$v['time']] = $v;
            }
        }

        //补全空数据
        $data = [];
        foreach($init_month as $key => $val){
            if(isset($count_data[$val['time']])){
                //当天数据 时间转化为整型
//                $data[$key]['time'] = $date == 'today' ? intval($count_data[$val['time']]['time']) : $count_data[$val['time']]['time'];
                $data[$key]['time'] = $count_data[$val['time']]['time'];
                $data[$key]['count'] = $count_data[$val['time']]['count'];
            } else {
                $data[$key]['time'] = $val['time'];
                $data[$key]['count'] = 0;
            }
        }

        $result = [
            'count' => $count,
            'count_node' => $data
        ];
        return $result;
    }


    /**
     * 获取粉丝取消关注数量
     * @param $sid
     * @param $date
     * @return array
     */
    public function getFansUnsubscribeCount($sid, $date){
        $result = [];//最终返回数据

        $where = [
            'sid' => $sid,
            'is_unsubscribe' => 1
        ];
        //查询今天 date=today、近7天 date=week、近30天 date=month
        $where_time = $date == 'today' ? 'today' : ($date == 'week' ? '-7 days' : '-30 days');

        //查询时间段内的新增数量
        $count = db('user_integral')
                    ->where($where)
                    ->whereTime('unsubscribe_time', '>', $where_time)
                    ->count();


        if($date == 'today'){
            //初始化数据
            $init_month = $this->initDayTime();
            //以不同的节点分组
            //存在8h误差
            $field = 'FROM_UNIXTIME(unsubscribe_time+8*60*60, \'%k\') as time,count(*) as count';
        } elseif($date == 'week'){
            //初始化数据
            $init_month = $this->initWeekTime();
            //以不同的节点分组
            $field = 'FROM_UNIXTIME(unsubscribe_time, \'%m-%d\') as time,count(*) as count';
        } elseif($date == 'month'){
            //初始化数据
            $init_month = $this->initMonthTime();
            //以不同的节点分组
            $field = 'FROM_UNIXTIME(unsubscribe_time, \'%m-%d\') as time,count(*) as count';
        }

        //如果增量为0，则直接返回初始化数据
        if($count == 0){
            $result = [
                'count' => 0,
                'count_node' => $init_month
            ];
            return $result;
        }

        //查询时间段内每个时间节点的增量
        $count_node = db('user_integral')
            ->field($field)
            ->where($where)
            ->whereTime('unsubscribe_time', '>', $where_time)
            ->group('time')
            ->select();


        //保存以日期为key的数据
        $count_data = [];
        if($count_node){
            foreach($count_node as $k => $v){
                $count_data[$v['time']] = $v;
            }
        }

        //补全空数据
        $data = [];
        foreach($init_month as $key => $val){
            if(isset($count_data[$val['time']])){
                //当天数据 时间转化为整型
                $data[$key]['time'] = $date == 'today' ? intval($count_data[$val['time']]['time']) : $count_data[$val['time']]['time'];
                $data[$key]['count'] = $count_data[$val['time']]['count'];
            } else {
                $data[$key]['time'] = $val['time'];
                $data[$key]['count'] = 0;
            }
        }

        $result = [
            'count' => $count,
            'count_node' => $data
        ];
        return $result;
    }




    /**
     * 初始化今天每个小时的数据
     * @return array
     */
    public function initDayTime(){
        $data = [];
        for($i = 0; $i < 24; $i++){
            //包含今天
            $data[$i]['time'] = $i;
            $data[$i]['count'] = 0;
        }
        return $data;
    }


    /**
     * 初始化近7天里每天的数据
     * @return array
     */
    public function initWeekTime(){
        $data = [];
        for($i = 0; $i < 7; $i++){
            //包含今天
            $data[$i]['time'] = date('m-d', strtotime('-'.(6 - $i).' day'));
            $data[$i]['count'] = 0;
        }
        return $data;
    }


    /**
     * 初始化近30天里每天的数据
     * @return array
     */
    public function initMonthTime(){
        $data = [];
        for($i = 0; $i < 30; $i++){
            //包含今天
            $data[$i]['time'] = date('m-d', strtotime('-'.(29 - $i).' day'));
            $data[$i]['count'] = 0;
        }
        return $data;
    }

    /**
     * 会员统计
     * @param $sid
     * @param int $type 0-今天，1-7天，2-30天
     */
    public function MemberStatistics($sid,$type = 0){
        //开始时间
        $starttime = mktime(0, 0, 0, date('m'), date('d'), date('Y'));
        //查询方式
        $time_type = '%k';
        if($type ==0){
            for ($x = 0; $x < 24; $x++) {
                $list[$x] = 0;
            }
        }
        if($type == 1){
            $starttime = mktime(0, 0, 0, date('m'), date('d')-6, date('Y'));
            $time_type = '%m-%d';
            for ($x = 0; $x < 7; $x++) {
                $key = date("m-d",strtotime("-".$x." day"));
                $list[$key] = 0;
            }
        }
        if($type == 2){
            $starttime = mktime(0, 0, 0, date('m'), date('d')-29, date('Y'));
            $time_type = '%m-%d';
            for ($x = 0; $x < 30; $x++) {
                $key = date("m-d",strtotime("-".$x." day"));
                $list[$key] = 0;
            }
        }
        $list = array_reverse($list);
        //粉丝数量
        $fans_where = [
            'sid' => $sid,
            'addtime' => ['>',$starttime],
            'students_id' => 0,
            'counselor_status' => 0
        ];
        $fans_num = db('user_integral')->where($fans_where)->count();
        $fans_list = db('user_integral')
            ->field('FROM_UNIXTIME(addtime+8*60*60,\''.$time_type.'\') as time,count(*) as count')
            ->where($fans_where)
            ->group('time')
            ->select();
        $temp = $list;
        if(!empty($fans_list)){
            foreach($fans_list as $v){
                $temp[$v['time']] = $v['count'];
            }
        }
        $i = 0;
        foreach($temp as $k => $v){
            $data['content']['line']['fans'][$i]['time'] = $k;
            $data['content']['line']['fans'][$i]['count'] = $v;
            $i++;
        }
        //未签单客户数量
        $nosign_where = [
            'ui.sid' => $sid,
            's.addtime' => ['>',$starttime],
            'ui.students_id' => ['>',0],
            'ui.counselor_status' => 0,
            's.formal_status' => 0
        ];
        $nosign_num = db('user_integral')->alias('ui')->join('students s','s.id = ui.students_id')->where($nosign_where)->count();
        $nosign_list = db('user_integral')
            ->alias('ui')
            ->join('students s','s.id = ui.students_id')
            ->where($nosign_where)
            ->field('FROM_UNIXTIME(s.addtime+8*60*60,\''.$time_type.'\') as time,count(*) as count')
            ->group('time')
            ->select();
        $temp = $list;
        if(!empty($nosign_list)){
            foreach($nosign_list as $v){
                $temp[$v['time']] = $v['count'];
            }
        }
        $i = 0;
        foreach($temp as $k => $v){
            $data['content']['line']['nosign'][$i]['time'] = $k;
            $data['content']['line']['nosign'][$i]['count'] = $v;
            $i++;
        }
        //签单客户数量
        $sign_where = [
            'ui.sid' => $sid,
            's.sign_addtime' => ['>',$starttime],
            'ui.students_id' => ['>',0],
            'ui.counselor_status' => 0,
            's.formal_status' => 1
        ];
        $sign_num = db('user_integral')->alias('ui')->join('students s','s.id = ui.students_id')->where($sign_where)->count();
        $sign_list = db('user_integral')
            ->alias('ui')
            ->join('students s','s.id = ui.students_id')
            ->where($sign_where)
            ->field('FROM_UNIXTIME(s.sign_addtime+8*60*60,\''.$time_type.'\') as time,count(*) as count')
            ->group('time')
            ->select();
        $temp = $list;
        if(!empty($sign_list)){
            foreach($sign_list as $v){
                $temp[$v['time']] = $v['count'];
            }
        }
        $i = 0;
        foreach($temp as $k => $v){
            $data['content']['line']['sign'][$i]['time'] = $k;
            $data['content']['line']['sign'][$i]['count'] = $v;
            $i++;
        }
        //顾问数量
        $counselor_where = [
            'sid' => $sid,
            'counselor_edittime' => ['>',$starttime],
            'counselor_status' => 1
        ];
        $counselor_num = db('user_integral')->where($counselor_where)->count();
        $counselor_list = db('user_integral')
            ->where($counselor_where)
            ->field('FROM_UNIXTIME(counselor_edittime+8*60*60,\''.$time_type.'\') as time,count(*) as count')
            ->group('time')
            ->select();
        $temp = $list;
        if(!empty($counselor_list)){
            foreach($counselor_list as $v){
                $temp[$v['time']] = $v['count'];
            }
        }
        $i = 0;
        foreach($temp as $k => $v){
            $data['content']['line']['counselor'][$i]['time'] = $k;
            $data['content']['line']['counselor'][$i]['count'] = $v;
            $i++;
        }

        //饼状图
        $data['content']['pie'] = [
            '0' => [
                'type_name' => '粉丝数量',
                'profit' => $fans_num
            ],
            '1' => [
                'type_name' => '未签单客户数量',
                'profit' => $nosign_num
            ],
            '2' => [
                'type_name' => '签单客户数量',
                'profit' => $sign_num
            ],
            '3' => [
                'type_name' => '顾问数量',
                'profit' => $counselor_num
            ],
        ];
        return $data;
    }

    /**
     * 首页数据获取
     * @param $sid
     * @return mixed
     */
    public function getDataStatistics($sid){
        //公众号粉丝数量
        $public_list = WeixinHelper::getUserTotal($sid);
        $data['content']['public'] = $public_list;
        //系统会员
        $data['content']['system'] = db('user_integral')->where(['sid' => $sid])->count();
        //粉丝数量
        $fans_where = [
            'sid' => $sid,
            'students_id' => 0,
            'counselor_status' => 0
        ];
        $data['content']['fans'] = db('user_integral')->where($fans_where)->count();
        //意向客户
        $nosign_where = [
            'ui.sid' => $sid,
            'ui.students_id' => ['>',0],
            'ui.counselor_status' => 0,
            's.formal_status' => 0
        ];
        $data['content']['nosign'] = db('user_integral')->alias('ui')->join('students s','s.id = ui.students_id')->where($nosign_where)->count();
        //签单客户
        $sign_where = [
            'ui.sid' => $sid,
            'ui.students_id' => ['>',0],
            'ui.counselor_status' => 0,
            's.formal_status' => 1
        ];
        $data['content']['sign'] = db('user_integral')->alias('ui')->join('students s','s.id = ui.students_id')->where($sign_where)->count();
        //顾问
        $counselor_where = [
            'sid' => $sid,
            'counselor_status' => 1
        ];
        $data['content']['counselor'] = db('user_integral')->where($counselor_where)->count();
        $visiting_where = [
            'store_id' => $sid
        ];
        //今日拜访
        $starttime = mktime(0, 0, 0, date('m'), date('d'), date('Y'));
        $endtime = mktime(23, 59, 59, date('m'), date('d'), date('Y'));
        $data['content']['today'] = db('visiting')->where($visiting_where)->whereBetween('next_time',[$starttime,$endtime])->group('uid')->count();

        //明日拜访
        $starttime = mktime(0, 0, 0, date('m'), date('d')+1, date('Y'));
        $endtime = mktime(23, 59, 59, date('m'), date('d')+1, date('Y'));
        $data['content']['tomorrow'] = db('visiting')->where($visiting_where)->whereBetween('next_time',[$starttime,$endtime])->group('uid')->count();
        return $data;
    }


    /**
     * 获取内容页面点击量和分享量
     * @param $sid
     * @return array
     */
    public function getMagazineCount($sid){
        //点击量
        $pv_list = db('magazine')->field('title,pv')
                    ->where(['sid' => $sid, 'status' => 1])
                    ->order('pv DESC')
                    ->limit(0,10)
                    ->select();

        //分享量
        $share_field = 'm.title,COUNT(*) as share_count';
        $where = [
            'sl.sid' => $sid,
            'sl.share_type' => 'success',
//            'sl.c' => 'Magazine',
            'sl.a' => 'magazine.html',
            'sl.cid' => 1
        ];
        $share_list = db('share_log sl')
                        ->field($share_field)
                        ->join('magazine m', 'sl.data_id = m.mid', 'LEFT')
                        ->where($where)
                        ->order('share_count DESC')
                        ->group('data_id')
                        ->limit(0,10)
                        ->select();

        $data = [
            'pv_list' => $pv_list,
            'share_list' => $share_list
        ];
        return $data;
    }

}